# !/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time      :   2021/3/29 19:12
# @Author    :   WenMing
# @Site      :   
# @File      :   PyMysqlTest.py
# @Desc      :   
# @Software  :   PyCharm
# @license   :   Copyright(C), C
# @Contact   :   736038880@qq.com
import pymysql
import json

class PyMysqlTest(object):

    def __init__(self, table):
        try:
            self.db = pymysql.connect(host="192.168.39.101", user="wenming", password="123", database="stock", port=3306)
            self.table = table
        except Exception as e :
            print('------------------db init error----------------')
            print(e)

    #单条插入
    def insert(self, params={}):
        if params:
            p_keys = ','.join(params.keys())
            p_values = ', '.join(['%s'] * len(params))

            cursor = self.db.cursor()

            sql = "INSERT INTO `{table}` ({keys}) VALUES ({values})".format(table=self.table, keys=p_keys, values=p_values)
            try:
                if cursor.execute(sql, tuple(params.values())):
                    self.db.commit()
            except Exception as e:
                print('-------------mysql-errors------------')
                print(e)
                self.db.rollback()

    #批量插入
    def create(self, params=[]):

        if params:
            cursor = self.db.cursor()
            for value in params:
                p_keys = ','.join(value.keys())
                p_values = ', '.join(['%s'] * len(value))
            sql = "INSERT INTO `{table}` ({keys}) VALUES ({values})".format(table=self.table, keys=p_keys, values=p_values)
            # 区别与单条插入数据，VALUES ('%s', '%s',  %s,  '%s', %s) 里面不用引号

            val = tuple(tuple(item.values()) for item in params)
            try:
                # 执行sql语句
                cursor.executemany(sql, val)
                # 提交到数据库执行
                self.db.commit()
            except:
                # 如果发生错误则回滚
                self.db.rollback()
        else:
            print('params not null')

    def create_batch(self, data:(list,dict)):
        if data:
                # value值列表
                value_data = []
                #如果是列表，则批量添加
                if isinstance(data,list):

                    #下标0设置表名以及字段信息
                    p_keys = ','.join(data[0].keys())
                    sql = "INSERT INTO `{table}` ({keys}) VALUES ".format(table=self.table, keys=p_keys)
                    #跳过第一次循环
                    for value in data:
                        p_values = ', '.join(['%s'] * len(value))
                        sql += "({values}),".format(values=p_values)
                        value_data.extend(value.values())
                    sql = sql.strip(',')
                else:
                    p_keys = ','.join(data.keys())
                    p_values = ', '.join(['%s'] * len(data))
                    sql = "INSERT INTO `{table}` ({keys}) VALUES ({values})".format(table=self.table, keys=p_keys,values=p_values)

                try:
                    cursor.execute(sql, value_data)
                    self.db.commit()
                    print('批量插入成功')
                except Exception as e:
                    self.db.rollback()
                    print(e)
        else:
            print('params not null')

        return True

    #更新数据
    def update(self, params=[], table=''):
        pass

    #清空表
    def truncate_table(self):
        try:
            cursor = self.db.cursor()
            sql = 'truncate table '+self.table
            cursor.execute(sql)
        except Exception as e:
            print('---------error--------')
            print(e)

    def __del__(self):
        self.db.close()